package Repositorio;

import View.Endereco;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.swing.table.DefaultTableModel;

public class RepositorioCliente {

    private Connection conn;
    private String query;
    private PreparedStatement stmt;
    private ResultSet rs;

    public void verifyAddress(String empresa_ref) throws SQLException, ClassNotFoundException {
        conn = LinkDB.Conexao.getConexao();
        query = "SELECT cliente.nameCliente, cliente.endereco FROM cliente "
                + "WHERE cliente.nameCliente LIKE CONCAT('%', ?, '%') "
                + "GROUP BY cliente.endereco";
        stmt = conn.prepareStatement(query);
        stmt.setString(1, empresa_ref);
        rs = stmt.executeQuery();
        DefaultTableModel model = (DefaultTableModel) Endereco.tabelaVariavel.getModel();
        while (rs.next()) {
            model.addRow(new Object[]{
                        rs.getString("cliente.nameCliente"),
                        rs.getString("cliente.endereco")
                    });
        }
        rs.close();
        stmt.close();
        conn.close();
    }

    public void salvarCliente(String Cliente, String endereco, String contato,
            String setor, String telefone) throws SQLException {
        conn = LinkDB.Conexao.getConexao();
        query = "INSERT INTO cliente(nameCliente, endereco, contactCliente, setor, tellCliente) "
                + "VALUES(?, ?, ?, ?, ?)";
        stmt = conn.prepareStatement(query);
        stmt.setString(1, Cliente);
        stmt.setString(2, endereco);
        stmt.setString(3, contato);
        stmt.setString(4, setor);
        stmt.setString(5, telefone);
        stmt.executeUpdate();
        stmt.close();
        conn.close();
    }

    public int idCliente(String nameCliente, String endereco, String contato,
            String setor, String telefone) throws SQLException {
        conn = LinkDB.Conexao.getConexao();
        query = "SELECT idCliente FROM cliente WHERE nameCliente = ? "
                + "AND endereco = ? "
                + "AND contactCliente = ? "
                + "AND setor = ? "
                + "AND tellCliente = ?";
        stmt = conn.prepareStatement(query);
        stmt.setString(1, nameCliente); 
        stmt.setString(2, endereco);
        stmt.setString(3, contato);
        stmt.setString(4, setor);
        stmt.setString(5, telefone);
        rs = stmt.executeQuery();
        while (rs.next()) {
            return Integer.parseInt(rs.getString("idCliente"));
        }
        rs.close();
        stmt.close();
        conn.close();
        return 0;
    }

    public boolean verifyClienteExist(String nameCliente, String contatoCliente,
            String setorCliente, String enderecoCliente, String telefoneCliente) throws SQLException {
        conn = LinkDB.Conexao.getConexao();
        query = "SELECT * FROM cliente WHERE nameCliente = ? "
                + "AND contactCliente = ? AND setor = ? AND endereco = ?"
                + "AND tellCliente = ?";
        stmt = conn.prepareStatement(query);
        stmt.setString(1, nameCliente);
        stmt.setString(2, contatoCliente);
        stmt.setString(3, setorCliente);
        stmt.setString(4, enderecoCliente);
        stmt.setString(5, telefoneCliente);
        rs = stmt.executeQuery();
        while (rs.next()) {
            return true;
        }
        rs.close();
        stmt.close();
        conn.close();
        return false;
    }
    
}
